// shop.js
var express = require('express');
var UserRouter = express.Router();
// 引入数据库连接池
const db = require('../db/index');
UserRouter.get('/adminapi/getShopList', function(req, res, next) {
    console.log(req.query);
    
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const offset = (page - 1) * limit;
    const commodityName = req.query.name || '';
    const keyVal = req.query.keyVal || '';
    const order = req.query.order || ''; // "ascending" 或 "descending"

    // 构建查询条件
    let query = 'SELECT * FROM jewelry_list';
    let countQuery = 'SELECT COUNT(*) AS total FROM jewelry_list';
    let queryParams = [];

    if (commodityName) {
        query += ' WHERE CommodityName LIKE ?';
        countQuery += ' WHERE CommodityName LIKE ?';
        queryParams.push(`%${commodityName}%`);
    }
    // 只有在 keyVal 和 order 都不为空时才进行排序
    if (keyVal) {
        if (order!=null) {
            query += ' ORDER BY ' + db.escapeId(keyVal);
            if (order === 'descending') {
                query += ' DESC';
            } else if (order === 'ascending') {
                query += ' ASC';
            }
        }
        
    }

    query += ' LIMIT ? OFFSET ?';
    queryParams.push(limit, offset);

    // 查询总记录数
    db.query(countQuery, queryParams.slice(0, -2))
        .then(([countResult]) => {
            const total = countResult[0].total;
            const totalPages = Math.ceil(total / limit);

            // 查询分页数据
            return db.query(query, queryParams)
                .then(([results]) => {
                    // console.log('Query results:', results);
                    let data = {
                        code: 200,
                        msg: '查询成功',
                        list: results,
                        page: page,
                        pageSize: limit,
                        total: total,
                        totalPages: totalPages
                    };
                    res.json(data);
                });
        })
        .catch(err => {
            console.error('Error executing query:', err.stack);
            res.status(500).send('未查询到数据');
        });
});

module.exports = UserRouter;